package servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import otherObjects.DBConn;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class for Servlet: Lancement
 *
 */
 public class Lancement extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
   static final long serialVersionUID = 1L;
   private Connection conn;
   private String nombd;
   
    /* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	public Lancement() {
		super();
	}   	
	
	@Override
	public void destroy() {
		// TODO Auto-generated method stub
		super.destroy();
		this.destructionTableAlert(nombd,"Alert");
		this.destructionTableMemory(nombd,"Memory");
	}

	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}  	
	
	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	@Override
	public void init(ServletConfig config) throws ServletException {
		// TODO Auto-generated method stub
		super.init(config);
		try {	
			DBConn dbc=new DBConn();
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			String url="jdbc:derby:live;create=true";
			Properties prop=new Properties();
			prop.setProperty("user","");
			prop.setProperty("password","");
			conn=DriverManager.getConnection(url,prop);
			dbc.setConnexion(conn);
			nombd=config.getInitParameter("dbname");
			this.creationTableMemory(nombd, "Memory");
			this.creationTableAlert(nombd, "Alert");
			ServletContext sc=config.getServletContext();
			sc.setAttribute("connexionBD",dbc);
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println(e.getMessage());
		}catch (SQLException e){
			System.out.println(e.getMessage());
		}
	}
	
	public void creationTableMemory(String nomBD, String nomTable){
		try{
			DatabaseMetaData dmd = conn.getMetaData();
			ResultSet tables = dmd.getTables(null,null,nomTable.toUpperCase(),null);
			if(!tables.next()){
				//la table n'existe pas donc on la crée
				
				String query = "CREATE TABLE Memory(" +
							"pseudo DOUBLE," +
							"time TIMESTAMP," +
							"id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY)";
				Statement pst = conn.createStatement() ;
				pst.executeUpdate(query);
				log ("Création Memory OK");
			}
			else{
				log ("La table Memory existe deja merci !!");
			}
		}catch (Exception e){
			log ("creation Table Memory "+ e.getMessage());
		}
	}
	
	public void destructionTableMemory(String nomBD, String nomTable){
		try{
			DatabaseMetaData dmd = conn.getMetaData();
			ResultSet tables = dmd.getTables(null,null,nomTable.toUpperCase(),null);
			if(tables.next()){
				//la table n'existe pas donc on la crée
				
				String query = "DROP TABLE Memory";
				Statement pst = conn.createStatement() ;
				pst.executeUpdate(query);
				log ("Destruction Memory OK");
			}
			else{
				log ("La table Memory n'existe pas !!");
			}
		}catch (Exception e){
			log ("destruction Table Memory "+ e.getMessage());
		}
	}
	
	public void creationTableAlert(String nomBD, String nomTable){
		try{
			DatabaseMetaData dmd = conn.getMetaData();
			ResultSet tables = dmd.getTables(null,null,nomTable.toUpperCase(),null);
			if(!tables.next()){
				//la table n'existe pas donc on la crée
				
				String query = "CREATE TABLE Alert(" +
							"date TIMESTAMP," +
							"mail VARCHAR(50)," +
							"value DOUBLE,"+
							"limit DOUBLE,"+
							"component VARCHAR(20),"+
							"id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY)";
				Statement pst = conn.createStatement() ;
				pst.executeUpdate(query);
				log ("Création Alert OK");
			}
			else{
				log ("La table Alert existe deja merci !!");
			}
		}catch (Exception e){
			log ("creation Table Alert "+ e.getMessage());
		}
	}
	
	public void destructionTableAlert(String nomBD, String nomTable){
		try{
			DatabaseMetaData dmd = conn.getMetaData();
			ResultSet tables = dmd.getTables(null,null,nomTable.toUpperCase(),null);
			if(tables.next()){
				//la table n'existe pas donc on la crée
				
				String query = "DROP TABLE Alert";
				Statement pst = conn.createStatement() ;
				pst.executeUpdate(query);
				log ("Destruction Alert OK");
			}
			else{
				log ("La table Alert n'existe pas !!");
			}
		}catch (Exception e){
			log ("destruction Table Alert "+ e.getMessage());
		}
	}
}